﻿-- DROP INSERT
CREATE PROCEDURE [todoc].[sspDropInsertSP]
(
	@TableSchema SYSNAME,
	@TableName SYSNAME,
	@Debug BIT = 0
)
AS
/*
GENERATES THE SQL FOR THE DROP OF AN INSERT STORED PROCEDURE

DATE: MARCH 2013

WRITTEN BY JAMES SKIPWITH, http://sqlpimp.com/

YOU MAY ALTER THIS CODE AS YOU WISH. KNOCK YOURSELF OUT BUT...THIS CODE AND 
INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED 
OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY 
AND/OR FITNESS FOR A PARTICULAR PURPOSE.
*/
DECLARE @SQL NVARCHAR(MAX) = '';
DECLARE @InsertProcNamePrefix NVARCHAR(20) = '';
DECLARE @InsertProcNamePostfix NVARCHAR(20) = '';

BEGIN TRY

	-- get the postfix we're using and whether we are in maintenance mode
	SELECT	@InsertProcNamePrefix = MAX((CASE WHEN ep.[name] = N'Todoc: Procedure name prefix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END)),
			@InsertProcNamePostfix = MAX((CASE WHEN ep.[name] = N'Todoc: Procedure name postfix' THEN CONVERT(NVARCHAR(20), ep.[value]) ELSE '' END))
	FROM sys.extended_properties ep
	WHERE ep.[major_id] = OBJECT_ID('todoc.sspCreateInsertSP')
		AND ep.[name] IN('Todoc: Procedure name prefix', 'Todoc: Procedure name postfix');

	-- generate the drop
	SET @SQL += 'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[' + @TableSchema + '].[' + @InsertProcNamePrefix + @TableName + @InsertProcNamePostfix + ']'') AND type in (N''P'', N''PC''))' + char(13)
	SET @SQL += 'DROP PROCEDURE [' + @TableSchema + '].[' + @InsertProcNamePrefix + @TableName + @InsertProcNamePostfix + '];' + CHAR(13) + CHAR(13);
	
	IF (@Debug = 0)
	BEGIN
		EXEC sys.sp_executesql @SQL;
	END
	ELSE
	BEGIN
		PRINT @SQL;
	END

	RETURN 0;

END TRY

BEGIN CATCH

	DECLARE @ErrorMessage NVARCHAR(4000);
	DECLARE @ErrorSeverity INT;
	DECLARE @ErrorState INT;

	SELECT @ErrorMessage = ERROR_MESSAGE(), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();

	RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);

	RETURN -1;

END CATCH;